package uk.ac.ebi.pride.tools.pap;

import org.apache.log4j.Logger;
import uk.ac.ebi.pride.tools.pap.db.ConnectionConfigurationException;
import uk.ac.ebi.pride.tools.pap.db.ConnectionProviderFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


/**
 * @author Florian Reisinger
 * @since 0.1
 */
public class AccessionProvider {

    private static final Logger log = Logger.getLogger(AccessionProvider.class);

    // enumeration defining the supported use cases and their corresponding
    // table and row names in the DB (obviously dependent on the DB setup) 
    public enum AcCase {// NAME               TABLE                 ROW NAME
        EXPERIMENT(       "Experiment",      "pride_pap_exp",      "experiment"),
        PROJECT(          "Project",         "pride_pap_project",  "project"),
        IDENTIFICATION(   "Identification",  "pride_pap_ident",    "identification"),
        SPECTRA(          "Spectra",         "pride_pap_spectra",  "spectra"),
        PEPTIDE(          "Peptide",         "pride_pap_peptide",  "peptide"),
        PROTEOMEXCHANGE(  "Proteomexchange", "pride_pap_protexch", "proteomexchange"),
        MZML(             "mzML",            "pride_pap_mzml",     "mzml"),
        ANALYSISXML(      "analysisXML",     "pride_pap_axml",     "analysisxml");

        private String name;
        private String table;
        private String rowName;

        private AcCase(String name, String table, String rowName) {
            this.name = name;
            this.table = table;
            this.rowName = rowName;
        }

        public String getName() {
            return name;
        }

        public String getRowName() {
            return rowName;
        }

        public String getTable() {
            return table;
        }
    }

    ///// ///// ///// ///// ///// ///// ///// ///// ///// /////
    // public methods

    /**
     * This method does the job of retrieving the accession numbers from the database.
     * It uses the ConnectionProviderFactory to get the connection provider which in turn
     * will supply the connection to the database to use.
     * Note: unless the dirty read is prevented (with the preventDirtyRead flag) accession
     * numbers are not guaranteed to be consecutive.
     *
     * @param howMany      the number of accessions to retrieve.
     * @param query        the SQL query to use to access/retrieve the accession numbers. Note: from the
     *                     result of the query only the first int is read (result.getInt(1)).
     * @param serializable if set to true the connection will not auto-commit, but instead
     *                     the transaction isolation level is set to TRANSACTION_SERIALIZABLE
     *                     and committed maually after all statements have been executed.
     * @return a List of accession numbers as Long values.
     */
    public List<Long> getAccessionsOld(int howMany, String query, boolean serializable) {
        List<Long> accList = null;

        // try to get a connection to the database
        Connection connection = null;
        try {
            // in web container (tomcat) the ConnectionProviderFactory should have been
            // configured via a Listener before servlet initialisation.
            // in a local test environment this may have to be done manually!
            connection = ConnectionProviderFactory.getInstance().getConnectionProvider().getConnection();
        } catch (SQLException e) {
            log.error("SQLException while trying to establish a connection to the Oracle DB", e);
        } catch (ConnectionConfigurationException e) {
            log.error("Connection not configured!", e);
        }

        if (connection != null) {
            // assume we will retrieve accession numbers, so prepare the list
            accList = new ArrayList<Long>();
            // try to exectute the query over the connection
            try {
                if (serializable) { // prevent auto-commit, dirty reads, phantom reads and non-repeatable reads
                    connection.setAutoCommit(false);
                    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
                }
                Statement st = connection.createStatement();

                for (int i = 0; i < howMany; i++) {
                    ResultSet result = st.executeQuery(query);
                    result.next(); // get the first result
                    // add the retrieved accession to the list of accessions that will be returned to the user
                    accList.add((long) result.getInt(1));
                }
                if (serializable) {// otherwise set to auto-commit
                    connection.commit();
                    // set back to auto-commit
                    connection.setAutoCommit(true);
                }

            } catch (SQLException se) {
                log.error("SQLException while trying to execute query '" + query + "' on Oracle DB!", se);
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        log.error("Could not close connection to Oracle DB!", e);
                    }
                }
            }
        } else {
            log.error("Could not establish connection to database! Result will be null!");
        }

        return accList;
    }

    /**
     * Method to retrieve accession numbers for certain use cases.
     *
     * @param howMany number of required accessions.
     * @param useCase the case for which the accessions are required.
     * @return a list of howMany consecutive accession numbers for the specified use case
     *         or null in case less than one accession was requested.
     * @throws AccessionProviderException if the request could not be executed.
     */
    public List<Long> getAccessions(int howMany, AcCase useCase) throws AccessionProviderException {

        // if the request is for less that one accession (makes no sence), we return null
        if (howMany < 1) {
            return null;
        }

        long start = System.currentTimeMillis();
        List<Long> result = executeStatement(howMany, useCase);
        long stop = System.currentTimeMillis();
        // ToDo: remove time stuff in production version
        log.info("Generation of " + howMany + " " + useCase.getName() + " accessions took: " + (stop - start) + " ms.");

        return result;
    }


    ///// ///// ///// ///// ///// ///// ///// ///// ///// /////
    // private methods

    /**
     * This method does the job of retrieving the accession numbers from the database.
     * It uses the ConnectionProviderFactory to get the connection provider which in turn
     * will supply the connection to the database to use.

     * @param howMany number of required accessions.
     * @param useCase  the case for which the accessions are required.
     * @return a list of howMany consecutive accession numbers for the specified use case
     *         or null in case less than one accession was requested.
     * @throws AccessionProviderException .
     */
    private List<Long> executeStatement(int howMany, AcCase useCase) throws AccessionProviderException {
        assert(howMany > 0) : "The calling method should make sure that the howMany parameter is greater than 0!";

        // try to get a connection to the database
        Connection connection;
        try {
            // In web container (tomcat) the ConnectionProviderFactory should have been
            // configured via a Listener (ConfiguratorListener) before servlet initialisation.
            // In a local test environment this may have to be done manually!
            connection = ConnectionProviderFactory.getInstance().getConnectionProvider().getConnection();
        } catch (SQLException e) {
            log.error("SQLException while trying to establish a connection to the Oracle DB.", e);
            throw new AccessionProviderException("SQLException while trying to establish a connection to the Oracle DB.", e);
        } catch (ConnectionConfigurationException e) {
            log.error("Connection not configured!", e);
            throw new AccessionProviderException("Connection not configured!", e);
        }

        // if we could not get a connection, there is no point in going on
        if (connection == null) {
            throw new AccessionProviderException("Could not establish connection to database!");
        }

        // now try to get the accessions
        Statement st = null;
        ResultSet result = null;
        long current = 0L;
        try {
            // set up the connection to be on manual commit and read_committed transaction isolation level
            connection.setAutoCommit(false);
            if (connection.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
                connection.setTransactionIsolation( Connection.TRANSACTION_READ_COMMITTED );
            }

            st = connection.createStatement();
            // retrieve the next available accession
            // IMPORTANT: since the SELECT and UPDATE statments should not be interrupted to assure consistency,
            // we have to specify the FOR UPDATE option at the end of the SELECT. This will trigger a lock until
            // the transaction (after the UPDATE) is completed.
            result = st.executeQuery("SELECT current_ac FROM " + useCase.getTable() + " WHERE seq_name LIKE '" + useCase.getRowName() + "' FOR UPDATE");
            result.next(); // get the first result
            current = (long) result.getInt(1);
            // now increase the current value by the number of required accessions
            // e.g. set the current value to the next free accession
            long newValue = current + howMany;
            st.execute("UPDATE " + useCase.getTable() + " SET current_ac = " + newValue + " WHERE seq_name LIKE '" + useCase.getRowName() + "'");

            // manual commit, so both statements (SELECT and UPDATE) are within the same transaction
            connection.commit();

        } catch (SQLException se) {
                throw new AccessionProviderException("SQLException trying to execute SQL query.", se);
        } finally {
            try {
                if (result !=null) result.close();
                if (st != null) st.close();
                connection.close();
            } catch (SQLException e) {
                log.error("Could not properly close connection to Oracle DB!", e);
            }
        }

        // finally generate the individual, consecutive accessions starting from the retrieved accession
        List<Long> accList = new ArrayList<Long>();
        for (int i = 0; i < howMany; i++) {
            accList.add(current++);
        }

        return accList;
    }


}
